use student
go
create view xskkqk
as
select sno,a.cno,cname,credit
from
(select sno,cno from xxxx) a left join (select * from kc) b on a.cno=b.cno

create view xsdkcj
as
select a.sno,c.sname,c.class,b.cname,a.grade
from
(select * from xxxx) a left join (select cno,cname from kc) b on a.cno=b.cno left join (select sno,sname,class from xsxx) c on a.sno=c.sno

create view xszcj
as
select a.sno,b.sname,b.class,a.sum_grade
from
(select sno,sum(grade) as sum_grade from xxxx group by sno) a left join (select sno,sname,class from xsxx) b on a.sno=b.sno

create view xscjxqpm
as
select xsxx.sno,sname,class,线性代数,政治经济原理,数据结构,英语,[C++],电子技术,计算机组成原理,计算机网络,哲学原理,数值分析,体育,总分,rank() over(order by 总分 desc) as '排名'
from
xsxx left join
(
select sno,
	sum(case when cname='线性代数' then grade end) as '线性代数',
	sum(case when cname='政治经济原理' then grade end) as '政治经济原理',
	sum(case when cname='数据结构' then grade end) as '数据结构',
	sum(case when cname='英语' then grade end) as '英语',
	sum(case when cname='C++' then grade end) as 'C++',
	sum(case when cname='电子技术' then grade end) as '电子技术',
	sum(case when cname='计算机组成原理' then grade end) as '计算机组成原理',
	sum(case when cname='计算机网络' then grade end) as '计算机网络',
	sum(case when cname='哲学原理' then grade end) as '哲学原理',
	sum(case when cname='数值分析' then grade end) as '数值分析',
	sum(case when cname='体育' then grade end) as '体育',
	sum(grade) as '总分'
from xsdkcj
group by sno) grades on xsxx.sno=grades.sno

create view xsdkcj2
as
select a.sno,b.cname,a.grade
from
(select * from xxxx) a left join (select cno,cname from kc) b on a.cno=b.cno

create view xscjwh
as
select a.sno,b.cno,a.sname,b.cname,a.sex,c.grade,a.age,b.credit,a.class
from
(select * from xsxx) a left join (select * from xxxx) c on a.sno=c.sno left join(select cno,cname,credit from kc) b on c.cno=b.cno